﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using mitasoft.Component.Dapper;
using System.Data.SqlClient;
using mitasoft.Component.Models.Blog;
using mitasoft.Component.Services.Interfaces;
using System.Data;

namespace mitasoft.Component.Services
{

    public class BlogService : BaseService, IBlogService
    {

        #region fields

        private readonly ICommonService _commonService;
        #endregion

        #region ctor

        public BlogService(ICommonService commonService)
        {
            _commonService = commonService;
        }
        #endregion


        #region Category

        public CategoryInfo AddCategory(string title)
        {
            return AddCategory(title, null);
        }
        public CategoryInfo AddCategory(string title, IDbTransaction transaction)
        {

            var existingCategory = Connection
                .Query<CategoryInfo>("select * from CategoryInfo where title = @Title",
                    param: new { Title = title }, buffered: false, transaction: transaction);

            if (existingCategory.Count() == 0)
            {
                var newCategory = new CategoryInfo
                {
                    Description = title,
                    Title = title
                };

                Connection.Insert(newCategory, transaction);
                return newCategory;
            }

            return null;
        }

        public CategoryInfo GetCategoryByTitle(string title)
        {

            var existingCategory = Connection
              .Query<CategoryInfo>("select * from CategoryInfo where title = @Title",
                  new { Title = title }, buffered: false);

            if (existingCategory.Count() > 0)
                return existingCategory.ElementAt(0);

            return null;
        }
        public CategoryInfo GetCategoryById(int id)
        {
            return Connection.Get<CategoryInfo>(id);
        }
        public CategoryInfo[] GetCategories()
        {
            return Connection.Query<CategoryInfo>("select * from CategoryInfo", buffered: false).ToArray();
        }

        #endregion

        #region Post

        public PostInfo NewPost(string title, string description, DateTime createdDate,
            bool publish, string[] categories)
        {

            SqlTransaction transaction = Connection.BeginTransaction();

            PostInfo post = new PostInfo();
            post.Title = title;
            post.Description = description;
            post.DateCreated = (createdDate == default(DateTime)) ? DateTime.Now : createdDate;
            post.Publish = publish;

            // new post here
            Connection.Insert<PostInfo>(post, transaction);

            if (categories.Count() > 0)
            {

                foreach (var categ in categories)
                {

                    //new categories if there aren't in DB
                    CategoryInfo pCategory = AddCategory(categ, transaction);

                    if (pCategory != null)
                    {

                        // new association between post and categories
                        Connection.Insert<PostCategory>(new PostCategory
                        {
                            CategoryId = pCategory.Id,
                            PostId = post.Id
                        }, transaction: transaction);
                    }
                }
            }

            transaction.Commit();
            return post;
        }

        public PostInfo GetPost(int id)
        {
            var post = Connection.Get<PostInfo>(id);
            var postCategories =
                Connection.Query<string>(
                    @"select ci.Title from PostCategory as pc 
                        inner join CategoryInfo as ci 
                            on pc.CategoryId = ci.Id 
                      where PostId=@Id",
                    new { Id = id }, buffered: false);

            post.Categories = postCategories.ToArray();
            return post;
        }

        public PostInfo UpdatePost(int id, string title, string description, DateTime createdDate,
            bool publish, string[] categories)
        {

            SqlTransaction transaction = Connection.BeginTransaction();

            var existingPost = Connection.Get<PostInfo>(id, transaction: transaction);
            existingPost.Title = title;
            existingPost.Description = description;
            existingPost.DateCreated = (createdDate == default(DateTime)) ? DateTime.Now : createdDate;
            existingPost.Publish = publish;

            // update post
            Connection.Update(existingPost, transaction: transaction);

            // TODO update categories too

            transaction.Commit();
            return existingPost;

        }

        #endregion
    }
}
